﻿package akkz;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.*;

public class StudentServer
{
	private boolean connectedState = false;
	private Connection conn = null;
	Statement state = null;
	ResultSet result = null;
	
	private String username;
	private String password;
	private String url;

	public StudentServer(String username, String password, String url)
	{
		this.username = username;
		this.password = password;
		this.url = url;
	}
	
	//==========db操作==========
	
	public void dbConnected()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			
			conn = DriverManager.getConnection(url, username, password);
			state = conn.createStatement();
			
			if ( !conn.isClosed() )
			{
				connectedState = true;
			}
		}
		catch (ClassNotFoundException ex)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null,ex);
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
		}
	}
	
	public void dbDisConnected()
	{
		try
		{
			if(result != null)
			{
				result.close();
			}
		}
		catch( SQLException e )
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		
		try
		{
			if(state != null)
			{
				state.close();
			}
		}
		catch( SQLException e )
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		
		try
		{
			if(conn != null)
			{
				conn.close();
			}
		}
		catch( SQLException e )
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
	}

	public boolean isConnected()
	{
		return connectedState;
	}
	
	//==========信息获取==========
	
	public String getName(int id)
	{
		try
		{
			dbConnected();
			
			result = state.executeQuery("SELECT name FROM student WHERE id = '" + id + "'");
	
			if(result.next())
				return result.getString(1);
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
		
		return null;
	}
	
	public Student getStudent(int id)
	{
		try
		{
			dbConnected();
			
			result = state.executeQuery("SELECT id,name,tel,mail,institute,major FROM student WHERE id = '" + id + "'");
			
			if(result.next())
			{
				Student student = new Student( result.getInt(1), result.getString(2), result.getString(3), result.getString(4), result.getString(5) + " " + result.getString(6));
								
				return student;
			}
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		catch (IndexOutOfBoundsException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			try
			{
				state.execute("UPDATA student SET tel = '0' WHERE id = '" + id + "'");
			}
			catch (SQLException ex)
			{
				Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, ex);
				
				throw new RuntimeException(ex);
			}
			finally
			{
				this.dbDisConnected();
			}
		}
		finally
		{
			this.dbDisConnected();
		}
		
		return null;
	}
	
	public Classes getClasses(String id)
	{
		try
		{
			dbConnected();
			
			Classes classes = null;
			
			result = state.executeQuery("SELECT * FROM classes WHERE id = '" + id + "'");
			
			if(result.next())
			{
				classes = new Classes(result.getInt(1), result.getString(2), result.getInt(3), result.getString(4), result.getString(5), result.getString(6));
			}
			
			return classes;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
	
	public boolean isJoin(int stuId, String classId)
	{
		try
		{
			dbConnected();
			
			result = state.executeQuery("SELECT * FROM classjoin WHERE stuid = '" + stuId + "' AND classid = '" + classId + "'");
			
			if(result.next())
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
	
	public ArrayList<Classes> getClasses(int id)
	{
		try
		{
			dbConnected();
			ArrayList<Classes> matches = new ArrayList<Classes>();
			
			result = state.executeQuery("SELECT * FROM classes WHERE id in (SELECT classid FROM classjoin WHERE stuid = '" + id + "')");
			
			while(result.next())
			{
				matches.add(new Classes(result.getInt(1), result.getString(2), result.getInt(3), result.getString(4), result.getString(5), result.getString(6)));
			}

			return matches;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
	
	public ArrayList<Classes> getJoinClasses(int id)
	{
		try
		{
			dbConnected();
			ArrayList<Classes> matches = new ArrayList<Classes>();
			
			result = state.executeQuery("SELECT * FROM classes WHERE id not in (SELECT classid FROM classjoin WHERE stuid = '" + id + "')");
			
			while(result.next())
			{
				matches.add(new Classes(result.getInt(1), result.getString(2), result.getInt(3), result.getString(4), result.getString(5), result.getString(6)));
			}

			return matches;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
	
	public void setStudnet(int id, String kind, String information)
	{
		try
		{
			dbConnected();
			
			state.executeUpdate("UPDATE student SET " + kind + " = '" + information + "' WHERE id = '" + id + "'");
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
	
	public String getTel(int id)
	{
		try
		{
			dbConnected();
			
			result = state.executeQuery("SELECT tel FROM student WHERE id = '" + id + "'");
			
			if(result.next())
			{
				String tel = result.getString(1);
				
				return tel;
			}
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
		
		return null;
	}
	
	public boolean regist(int stuid, int classid)
	{
		try
		{
			dbConnected();
			
			result = state.executeQuery("SELECT joined FROM classes where id = " + classid);
			int num = 0;
			
			if(result.next())
			{
				num = result.getInt(1);
			}
			
			if(num >= 45)
				return false;
				
			state.executeUpdate("INSERT INTO classjoin values(" + stuid + "," + classid + ")");
			state.executeUpdate("UPDATE classes SET joined = " + ++num + " WHERE id = " + classid);
			
			return true;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
		
	}
	
	public boolean cancle(int stuid, int classid)
	{
		try
		{
			dbConnected();
			
			result = state.executeQuery("SELECT joined FROM classes where id = " + classid);
			int num = 0;
			
			if(result.next())
			{
				num = result.getInt(1);
			}
			
			if(num <1)
				return false;
				
			state.executeUpdate("DELETE FROM classjoin WHERE stuid = " + stuid + " AND classid = " + classid);
			state.executeUpdate("UPDATE classes SET joined = " + --num + " WHERE id = " + classid);
			
			return true;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
		
	}
	
	public ArrayList<Classes> getAllClass()
	{
		try
		{
			dbConnected();
			
			ArrayList<Classes> classes = new ArrayList<Classes>();
			
			result = state.executeQuery("SELECT * FROM classes");
			
			while(result.next())
			{
				classes.add(new Classes(result.getInt(1), result.getString(2), result.getInt(3), result.getString(4), result.getString(5), result.getString(6)));
			}
			
			return classes;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
	
	public ArrayList<Student> getStudents(int classid)
	{
		try
		{
			dbConnected();
			
			ArrayList<Student> student = new ArrayList<Student>();
			
			result = state.executeQuery("SELECT id,name,tel,mail,institute,major FROM student WHERE id in ( SELECT stuid FROM classjoin where classid = " + classid + ")");
			
			while(result.next())
			{
				student.add(new Student( result.getInt(1), result.getString(2), result.getString(3), result.getString(4), result.getString(5) + " " + result.getString(6)));
			}
			
			return student;
		}
		catch (SQLException e)
		{
			Logger.getLogger(StudentServer.class.getName()).log(Level.SEVERE, null, e);
			
			throw new RuntimeException(e);
		}
		finally
		{
			this.dbDisConnected();
		}
	}
}
